﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Net;
using System.Collections;
using System.Xml;
using System.Linq;
//using System.Xml.Linq;

using CommonUtils;
using TDAL = EzDAL.Auto2;
using TDALUtils = EzDAL.Utils;
using TConst = TERP.Constants;
using TUtils = TERP.Utils;
using TError = TERP.Errors;


namespace TERP.BizITS
{
	public partial class BizWater
	{
		#region // Constructors and Destructors:
		public BizWater()
		{
		}

		private static object s_objLockCache = new object();
		private static TDAL.IEzDAL s_dbCache = null;
		private static CMySeq s_myseq = new CMySeq(CMySeq.c_strFormat, 10);

		private TUtils.WSTLog _log = null;

		private TDAL.IEzDAL _dbCarSv = null;
		private TDAL.IEzDAL _dbCarSv_Sys = null;

		private string _strConfig_GwUserCode = "";
		private string _strConfig_GwUserPassword = "";
		private string _strConfig_GwUserPasswordForLog = "";
		private bool _bDebugMode = true;

		public void LoadConfig(object[] arrobjParamsCouple)
		{
			// Init:
			Hashtable htConfig = CollectionUtils.ParamsCouple2Hashtable(true, arrobjParamsCouple);

			// Init Log:
			_log = new TUtils.WSTLog()
			{
				Url = string.Format("{0}", htConfig["_strConfig_Log_Url".ToUpper()]),
				Timeout = 123456000
			};

			// Init GWUser for authenticate:
			_strConfig_GwUserCode = string.Format("{0}", htConfig["_strConfig_GwUserCode".ToUpper()]);
			_strConfig_GwUserPassword = string.Format("{0}", htConfig["_strConfig_GwUserPassword".ToUpper()]);
			_strConfig_GwUserPasswordForLog = string.Format("{0}", htConfig["_strConfig_GwUserPasswordForLog".ToUpper()]);

			// Init _dbCarSv:
			if (s_dbCache == null)
			{
				lock (s_objLockCache)
				{
					if (s_dbCache == null)
					{
						TDAL.IEzDAL dbCache = new TDAL.EzDAL(string.Format("{0}", htConfig["_strConfig_DBConnStr_CarSv".ToUpper()]));
						dbCache.InitCacheManual();
						s_dbCache = dbCache; // Only caching when success.
					}
				}
			}
			_dbCarSv = (TDAL.IEzDAL)s_dbCache.Clone();
			_dbCarSv_Sys = (TDAL.IEzDAL)s_dbCache.Clone();
		}
		#endregion

//        #region // Private Utils:
//        #endregion

//        #region // Private Core:
//        private long myUtils_GetSeqRaw(string strTableName)
//        {
//            // Init:
//            string strSql = string.Format(@"
//					insert into {0} values (null);
//					delete {0} where AutoID = @@Identity;
//					select @@Identity Tid;
//				", strTableName
//            );
//            DataSet ds = _dbCarSv.ExecQuery(strSql);

//            // Return Good:
//            return Convert.ToInt64(ds.Tables[0].Rows[0][0]);
//        }
//        private string myCommon_GetNewId(
//            string strTableName
//            , string strPrefix
//            )
//        {
//            // Init:
//            long nSeq = myUtils_GetSeqRaw(strTableName);
//            string strTid = string.Format(
//                "{0}{1}.{2:000}"
//                , "" //strPrefix // {0}
//                , DateTime.Now.ToString("yyyyMMdd.HHmmss") // {1}
//                , nSeq % 1000 // {2}
//                );

//            // Return Good:
//            return strTid;
//        }
//        private void myUtils_ValidateId(
//            ref ArrayList alParamsCoupleError
//            , string strId
//            , string strErrCodeWhenInvalid
//            )
//        {
//            try
//            {
//                // Init:
//                TDAL.IEzDAL db = (TDAL.IEzDAL)s_dbCache.Clone();
//                string strSql = @"insert into Sys_ValidateId values (@Id);";
//                db.ExecQuery(
//                    strSql
//                    , "@Id", strId
//                    );
//            }
//            catch (Exception exc)
//            {
//                throw CMyException.Raise(
//                    strErrCodeWhenInvalid // strErrorCode
//                    , exc // excInner
//                    , alParamsCoupleError.ToArray() // arrobjParamsCouple
//                    );
//            }
//        }
//        private void myUtils_AuthenticateGW(
//            ref ArrayList alParamsCoupleError
//            , string strGWUserCode
//            , string strGWPassword
//            , string strTid
//            , string strPartnerCode
//            , string strPartnerUserCode
//            , string strErrCodeWhenInvalid
//            )
//        {
//            if (!StringUtils.StringEqualIgnoreCase(strGWUserCode, _strConfig_GwUserCode)
//                || !StringUtils.StringEqual(strGWPassword, _strConfig_GwUserPassword)
//                )
//            {
//                throw CMyException.Raise(
//                    strErrCodeWhenInvalid // strErrorCode
//                    , null // excInner
//                    , alParamsCoupleError.ToArray() // arrobjParamsCouple
//                    );
//            }
//        }
//        private string myUtils_GetParamsRaw(string strParamCode)
//        {
//            // Init:
//            string strSql = @"
//					select top 1
//						t.ParamValue
//					from Mst_Param t with (nolock)
//					where
//						t.ParamCode = @ParamCode
//					;
//				";
//            DataSet ds = _dbCarSv.ExecQuery(
//                strSql
//                , "@ParamCode", strParamCode
//                );

//            // Return Good:
//            return Convert.ToString(ds.Tables[0].Rows[0][0]);
//        }
//        #endregion

		#region // Utils.myUtils:
		private long myUtils_GetSeqRaw(string strTableName)
		{
			// Init:
			string strSql = string.Format(@"
					insert into {0} values (null);
					delete {0} where AutoID = @@Identity;
					select @@Identity Tid;
				", strTableName
			);
			DataSet ds = _dbCarSv.ExecQuery(strSql);

			// Return Good:
			return Convert.ToInt64(ds.Tables[0].Rows[0][0]);
		}
		private string myCommon_GetNewId(
			string strTableName
			, string strPrefix
			)
		{
			// Init:
			long nSeq = myUtils_GetSeqRaw(strTableName);
			string strTid = string.Format(
				"{0}{1}.{2:000}"
				, "" //strPrefix // {0}
				, DateTime.Now.ToString("yyyyMMdd.HHmmss") // {1}
				, nSeq % 1000 // {2}
				);

			// Return Good:
			return strTid;
		}
		private void myUtils_ValidateId(
			ref ArrayList alParamsCoupleError
			, string strId
			, string strErrCodeWhenInvalid
			)
		{
			try
			{
				// Init:
				TDAL.IEzDAL db = (TDAL.IEzDAL)s_dbCache.Clone();
				string strSql = @"insert into Sys_ValidateId values (@Id);";
				db.ExecQuery(
					strSql
					, "@Id", strId
					);
			}
			catch (Exception exc)
			{
				throw CMyException.Raise(
					strErrCodeWhenInvalid // strErrorCode
					, exc // excInner
					, alParamsCoupleError.ToArray() // arrobjParamsCouple
					);
			}
		}
		private void myUtils_AuthenticateGW(
			ref ArrayList alParamsCoupleError
			, string strGWUserCode
			, string strGWPassword
			, string strTid
			, string strPartnerCode
			, string strPartnerUserCode
			, string strErrCodeWhenInvalid
			)
		{
			if (!StringUtils.StringEqualIgnoreCase(strGWUserCode, _strConfig_GwUserCode)
				|| !StringUtils.StringEqual(strGWPassword, _strConfig_GwUserPassword)
				)
			{
				throw CMyException.Raise(
					strErrCodeWhenInvalid // strErrorCode
					, null // excInner
					, alParamsCoupleError.ToArray() // arrobjParamsCouple
					);
			}
		}
		private string myUtils_GetParamsRaw(string strParamCode)
		{
			// Init:
			string strSql = @"
					select top 1
						t.ParamValue
					from Mst_Param t with (nolock)
					where
						t.ParamCode = @ParamCode
					;
				";
			DataSet ds = _dbCarSv.ExecQuery(
				strSql
				, "@ParamCode", strParamCode
				);

			// Return Good:
			return Convert.ToString(ds.Tables[0].Rows[0][0]);
		}
		#endregion

		#region // Utils.myDebug:
		private void myDebug_SaveSql(
			bool bDebugMode
			, ref ArrayList alParamsCoupleError
			, string strParamName
			, string strSql
			)
		{
			if (bDebugMode)
			{
				alParamsCoupleError.AddRange(new object[]{
						strParamName, strSql
						});
			}
		}
		#endregion

		#region // Utils.myCommon:
		private bool myCommon_IsHTCDirect(DataRow drAbilityOfUser)
		{
			return (StringUtils.StringEqual(drAbilityOfUser["FlagDirect"], TConst.Flag.Active));
		}
		private ArrayList myCommon_GetSupportedTable()
		{
			ArrayList alTablesSupported = new ArrayList(new object[]{
					"GuideSupplier".ToUpper()
					, "Guide".ToUpper()
					, "FlightSupplier".ToUpper()
					, "Flight".ToUpper()
					, "VehicleSupplier".ToUpper()
					, "Vehicle".ToUpper()
					, "Restaurant".ToUpper()
					, "RestaurantMeal".ToUpper()
					, "VehicleSupplierMeal".ToUpper()
					, "Hotel".ToUpper()
					, "Hotel_Meal".ToUpper()
                    , "HotelRoom".ToUpper()
					, "CustomerSupplier".ToUpper()
					});
			return alTablesSupported;
		}
		private DataRow myCommon_GetAbilityOfUser(object strUserCode)
		{
			// Init:
			string strSql = StringUtils.Replace(@"
					select
						zzzzClauseColumnForSysUserInfo
						, md.*
					from Sys_User t with (nolock)
						inner join Mst_Dealer md with (nolock)
							on t.DealerCode = md.DealerCode
					where
						t.UserCode = @UserCode
					;
				"
				, "zzzzClauseColumnForSysUserInfo", mySql_GetClauseColumnForSysUserInfo("t.")
				);
			DataTable dt = _dbCarSv.ExecQuery(
				strSql // strSqlQuery
				, "@UserCode", strUserCode // arrParams couple items
				).Tables[0];

			// Return Good:
			return dt.Rows[0];
		}
		private void myCommon_CheckDataClosed(
			ref ArrayList alParamsCoupleError
			, object dtimeDataCreatedDate
			)
		{
			if (StringUtils.StringCompare(Convert.ToDateTime(dtimeDataCreatedDate).ToString("yyyyMM"), DateTime.Now.ToString("yyyyMM")) < 0
				&& (DateTime.Now - Convert.ToDateTime(dtimeDataCreatedDate)).TotalDays > 5 // In worst case: from last Friday to the next Monday.
				)
			{
				alParamsCoupleError.AddRange(new object[]{
					"DataCreatedDate", dtimeDataCreatedDate
					});
				throw CMyException.Raise(
					TError.ErrITS.CommonAppData_BizDataClosed
					, null
					, alParamsCoupleError.ToArray()
					);
			}
		}
        //private void myCommon_CheckPort(
        //    ref ArrayList alParamsCoupleError
        //    , object strPortCode
        //    , string strFlagExistToCheck
        //    , out DataTable dt_Mst_Port
        //    )
        //{
        //    // GetInfo:
        //    dt_Mst_Port = TDALUtils.DBUtils.GetTableContents(
        //        _dbCarSv // db
        //        , "Mst_Port" // strTableName
        //        , "top 1 *" // strColumnList
        //        , "" // strClauseOrderBy
        //        , "PortCode", "=", strPortCode // arrobjParamsTriple item
        //        );

        //    // strFlagExistToCheck:
        //    if (strFlagExistToCheck.Length > 0)
        //    {
        //        if (StringUtils.StringEqual(strFlagExistToCheck, TConst.Flag.Active) && dt_Mst_Port.Rows.Count < 1)
        //        {
        //            alParamsCoupleError.AddRange(new object[]{
        //                "PortCodeNotFound", strPortCode
        //                });
        //            throw CMyException.Raise(
        //                TError.ErrITS.CommonAppData_MstPortNotFound
        //                , null
        //                , alParamsCoupleError.ToArray()
        //                );
        //        }
        //        if (StringUtils.StringEqual(strFlagExistToCheck, TConst.Flag.Inactive) && dt_Mst_Port.Rows.Count > 0)
        //        {
        //            alParamsCoupleError.AddRange(new object[]{
        //                "PortCodeFound", strPortCode
        //                });
        //            throw CMyException.Raise(
        //                TError.ErrITS.CommonAppData_MstPortFound
        //                , null
        //                , alParamsCoupleError.ToArray()
        //                );
        //        }
        //    }
        //}


        //private void myCommon_CheckAccessDealerData(
        //    ref ArrayList alParamsCoupleError
        //    , object strBUPatternOfUser
        //    , object strBUCodeNeedToAccess
        //    )
        //{
        //    string strBUPatternCheck = Convert.ToString(strBUPatternOfUser).Replace("%", "");
        //    if (!Convert.ToString(strBUCodeNeedToAccess).StartsWith(strBUPatternCheck))
        //    {
        //        alParamsCoupleError.AddRange(new object[]{
        //            "DealerCodeCannotAccess", strBUCodeNeedToAccess
        //            });
        //        throw CMyException.Raise(
        //            TError.ErrITS.CommonAppData_MstDealerCannotAccess
        //            , null
        //            , alParamsCoupleError.ToArray()
        //            );
        //    }
        //}
        //private void myCommon_CheckSalesOrderType(
        //    ref ArrayList alParamsCoupleError
        //    , object strSalesOrderType
        //    //, out DataTable dt_Mst_SalesOrderType
        //    )
        //{
        //    //// GetInfo:
        //    //dt_Mst_SalesOrderType = TDALUtils.DBUtils.GetTableContents(
        //    //    _dbHTC // db
        //    //    , "Mst_SalesOrderType" // strTableName
        //    //    , "top 1 *" // strColumnList
        //    //    , "" // strClauseOrderBy
        //    //    , "SOType", "=", strSalesOrderType // arrobjParamsTriple item
        //    //    );

        //    //// bSalesOrderTypeExist:
        //    //if (dt_Mst_SalesOrderType.Rows.Count < 1)
        //    //{
        //    //    alParamsCoupleError.AddRange(new object[]{
        //    //        "SalesOrderTypeNotFound", strSalesOrderType
        //    //        });
        //    //    throw CMyException.Raise(
        //    //        TError.ErrITS.CommonAppData_MstSOTypeNotFound
        //    //        , null
        //    //        , alParamsCoupleError.ToArray()
        //    //        );
        //    //}

        //    // Validate:
        //    int nInvalidPos = ValidateUtils.ValidateParams(
        //        strSalesOrderType, true, new object[] { TConst.SalesOrderType.Plan, TConst.SalesOrderType.UnPlan }
        //        );
        //    if (nInvalidPos >= 0)
        //    {
        //        alParamsCoupleError.AddRange(new object[]{
        //            "SalesOrderTypeNotFound", strSalesOrderType
        //            });
        //        throw CMyException.Raise(
        //            TError.ErrITS.CommonAppData_MstSOTypeNotFound
        //            , null
        //            , alParamsCoupleError.ToArray()
        //            );
        //    }
        //}
        //private void myCommon_CheckMatchingModelAndSpecCode(
        //    ref ArrayList alParamsCoupleError
        //    , object strModelCode
        //    , object strSpecCode
        //    , out DataTable dt_Mst_ModelAdvancedOption
        //    )
        //{
        //    // GetInfo:
        //    dt_Mst_ModelAdvancedOption = TDALUtils.DBUtils.GetTableContents(
        //        _dbCarSv // db
        //        , "Mst_ModelAdvancedOption" // strTableName
        //        , "top 1 *" // strColumnList
        //        , "" // strClauseOrderBy
        //        , "ModelCode", "=", strModelCode // arrobjParamsTriple item
        //        , "SpecCode", "=", strSpecCode // arrobjParamsTriple item
        //        );

        //    // Not Matching:
        //    if (dt_Mst_ModelAdvancedOption.Rows.Count < 1)
        //    {
        //        alParamsCoupleError.AddRange(new object[]{
        //            "ModelCodeNotMatched", strModelCode
        //            , "SpecCodeNotMatched", strSpecCode
        //            });
        //        throw CMyException.Raise(
        //            TError.ErrITS.CommonAppData_MstModelNotMatchWithSpecCode
        //            , null
        //            , alParamsCoupleError.ToArray()
        //            );
        //    }
        //}
        //private void myCommon_CheckMatchingModelAndColor(
        //    ref ArrayList alParamsCoupleError
        //    , object strModelCode
        //    , object strColorCode
        //    , out DataTable dt_Mst_Color
        //    )
        //{
        //    // GetInfo:
        //    dt_Mst_Color = TDALUtils.DBUtils.GetTableContents(
        //        _dbCarSv // db
        //        , "Mst_Color" // strTableName
        //        , "top 1 *" // strColumnList
        //        , "" // strClauseOrderBy
        //        , "ModelCode", "=", strModelCode // arrobjParamsTriple item
        //        , "ColorCode", "=", strColorCode // arrobjParamsTriple item
        //        );

        //    // Not Matching:
        //    if (dt_Mst_Color.Rows.Count < 1)
        //    {
        //        alParamsCoupleError.AddRange(new object[]{
        //            "ModelCodeNotMatched", strModelCode
        //            , "ColorCodeNotMatched", strColorCode
        //            });
        //        throw CMyException.Raise(
        //            TError.ErrITS.CommonAppData_MstModelNotMatchWithColor
        //            , null
        //            , alParamsCoupleError.ToArray()
        //            );
        //    }
        //}
        //private void myCommon_CheckSpecCode(
        //    ref ArrayList alParamsCoupleError
        //    , object strSpecCode
        //    , string strFlagExistToCheck
        //    , out DataTable dt_Mst_ModelAdvancedOption
        //    )
        //{
        //    // GetInfo:
        //    dt_Mst_ModelAdvancedOption = TDALUtils.DBUtils.GetTableContents(
        //        _dbCarSv // db
        //        , "Mst_ModelAdvancedOption" // strTableName
        //        , "top 1 *" // strColumnList
        //        , "" // strClauseOrderBy
        //        , "SpecCode", "=", strSpecCode // arrobjParamsTriple item
        //        );

        //    // strFlagExistToCheck:
        //    if (strFlagExistToCheck.Length > 0)
        //    {
        //        if (StringUtils.StringEqual(strFlagExistToCheck, TConst.Flag.Active) && dt_Mst_ModelAdvancedOption.Rows.Count < 1)
        //        {
        //            alParamsCoupleError.AddRange(new object[]{
        //                "SpecCodeNotFound", strSpecCode
        //                });
        //            throw CMyException.Raise(
        //                TError.ErrITS.CommonAppData_MstSpecCodeNotFound
        //                , null
        //                , alParamsCoupleError.ToArray()
        //                );
        //        }
        //        if (StringUtils.StringEqual(strFlagExistToCheck, TConst.Flag.Inactive) && dt_Mst_ModelAdvancedOption.Rows.Count > 0)
        //        {
        //            alParamsCoupleError.AddRange(new object[]{
        //                "SpecCodeFound", strSpecCode
        //                });
        //            throw CMyException.Raise(
        //                TError.ErrITS.CommonAppData_MstSpecCodeFound
        //                , null
        //                , alParamsCoupleError.ToArray()
        //                );
        //        }
        //    }
        //}
		#endregion

		#region // Utils.mySql:
		private string mySql_GetClauseColumnForSysUserInfo(string strSysUserTableAlias)
		{
			return string.Format(@"
							{0}UserCode
							--, {0}DealerCode SUDealerCode
							, {0}UserName
							, N'{1}' UserPassword
							--, {0}FlagSysAdmin
							, {0}FlagActive SUFlagActive
				"
				, strSysUserTableAlias // {0}
				, TConst.HTCConst.PasswordTemplate // {1}
				);
		}
		private string mySql_GetClauseColumnForSysObjectInfo(string strSysObjectTableAlias)
		{
			return string.Format(@"
							{0}PartnerCode
							, {0}ObjectCode
							, {0}ObjectType
							, {0}ObjectName
							--, {0}ObjectCodeParent
							--, {0}ObjectCodeExec
							--, {0}PhysicalAssembly
							--, {0}PhysicalClass
							--, {0}FlagExecModal
							, {0}FlagActive SOFlagActive
				"
				, strSysObjectTableAlias
				);
		}
		private string mySql_GetClauseColumnForCarInfoBasic()
		{
			return @"
								t.CarId
								, t.SpecCode
								, t.ModelCode
								, t.ColorCode
								, t.DealerCode
								, t.UnitPriceInit
								, t.UnitPriceActual
								, t.PaymentStatus
								, t.DeliveryStatus
								--, t.MapVINRanking
								, t.FlagAllowChangeVIN
								, t.FlagActive
								, t.CreatedDate
								, t.CreatedBy
								, t.VIN
								, t.MapVINDate
								, t.MapVINBy
				";
		}
		private string mySql_GetClauseColumnForCarSpecInfo()
		{
			return @"
								, mmao.ModelCode MMAOModelCode
								, mmao.StandardCode
								, mmao.AdvancedCode
								, mmao.AdvDescription
								, mmso.StdDescription
								, (mmao.AdvDescription + ', ' + mmso.StdDescription) Description
								, mm.ModelProductionCode
								, mm.ModelName
								, mm.FlagActive MMFlagActive
								, mc.ColorExtType
								, mc.ColorExtCode
								, mc.ColorExtName
								, mc.ColorExtNameVN
								, mc.ColorIntCode
								, mc.ColorIntName
								, mc.ColorIntNameVN
								, mc.ColorFee
								, mc.Remark MCRemark
				";
		}
		private string mySql_GetClauseJoinForCarSpecInfo()
		{
			return @"
								inner join Mst_ModelAdvancedOption mmao with (nolock)
									on t.SpecCode = mmao.SpecCode
								inner join Mst_ModelStandardOption mmso with (nolock)
									on mmao.ModelCode = mmso.ModelCode and mmao.StandardCode = mmso.StandardCode
								inner join Mst_Model mm with (nolock)
									on mmao.ModelCode = mm.ModelCode
								inner join Mst_Color mc with (nolock)
									on t.ModelCode = mc.ModelCode and t.ColorCode = mc.ColorCode
				";
		}
		private string mySql_GetClauseColumnForVINInfo()
		{
			return @"
								, cpld.VIN CPLDVIN
								, cpld.PackingListNo CPLDPackingListNo
								, cpld.EngineNo CPLDEngineNo
								, cpld.KeyNo CPLDKeyNo
								, cpld.SpecCode CPLDSpecCode
								, cpld.ModelCode CPLDModelCode
								, cpld.ColorCode CPLDColorCode
								, cpld.StorageCode CPLDStorageCode
								, cpld.StoreDate CPLDStoreDate
								, cpld.CQStartDate CPLDCQStartDate
								, cpld.CQEndDate CPLDCQEndDate
								, cpld.CustomsClearanceDate CPLDCustomsClearanceDate
								, cpld.CODate CPLDCODate
								, cpld.DocumentsStatus CPLDDocumentsStatus
				";
		}
		private string mySql_GetClauseJoinForVINInfo()
		{
			return @"
								left join CT_PackingListDetail cpld with (nolock)
									on t.VIN = cpld.VIN
				";
		}
		private string mySql_GetClauseColumnForPackingListInfo()
		{
			return @"
								, cpl.PackingListNo CPLPackingListNo
								, cpl.LCNo CPLLCNo
								, cpl.DeclarationNo CPLDeclarationNo
								, cpl.PortCode CPLPortCode
								, cpl.ShippingDateStart CPLShippingDateStart
								, cpl.ShippingDateEnd CPLShippingDateEnd
								, clc.ContractNo CLCContractNo
				";
		}
		private string mySql_GetClauseJoinForPackingListInfo()
		{
			return @"
								left join CT_PackingList cpl with (nolock)
									on cpld.PackingListNo = cpl.PackingListNo
								left join CT_LC clc with (nolock)
									on clc.LCNo = cpl.LCNo
				";
		}
		private string mySql_GetClauseColumnForDealerInfo()
		{
			return @"
								, md.DealerCode MDDealerCode
								, md.DealerType MDDealerType
								, md.AreaCode MDAreaCode
								, md.BUCode MDBUCode
								, md.BUPattern MDBUPattern
								, md.DealerName MDDealerName
								, md.FlagActive MDFlagActive
				";
		}
		private string mySql_GetClauseJoinForDealerInfo()
		{
			return @"
								left join Mst_Dealer md with (nolock)
									on t.DealerCode = md.DealerCode
				";
		}
		private string mySql_GetClauseColumnForSalesOrderInfo()
		{
			return @"
								, oso.SOCode OSOSOCode
								, oso.SOType OSOSOType
								, oso.DealerCode OSODealerCode
								, oso.OrderMonth OSOOrderMonth
								, oso.ProductionMonth OSOProductionMonth
								, oso.ExpectedMonth OSOExpectedMonth
								, oso.SOStatus OSOSOStatus
				";
		}
		private string mySql_GetClauseJoinForSalesOrderInfo()
		{
			return @"
								left join Map_CC_OSO mccoso with (nolock)
									on t.CarId = mccoso.CarId
								left join Ord_SalesOrder oso with (nolock)
									on mccoso.SOCode = oso.SOCode
				";
		}
		private string mySql_GetClauseColumnForPaymentTotalInfo()
		{
			return @"
								, IsNull(cpdtotal.AmountTotal, 0.0) AmountTotal
				";
		}
		private string mySql_GetClauseJoinForPaymentTotalInfo()
		{
			return @"
								left join #tbl_CT_PaymentDetailTotal cpdtotal with (nolock)
									on t.CarId = cpdtotal.CarId
				";
		}
		private string mySql_GetClauseSelect_CachingForPaymentTotal()
		{
			return mySql_GetClauseSelect_CachingForPaymentTotal(@"#tbl_CT_PaymentDetailTotal");
		}
		private string mySql_GetClauseSelect_CachingForPaymentTotal(string strTableNameTemp)
		{
			return StringUtils.Replace(@"
						select
							t.CarId
							, Sum(cpd.Amount) AmountTotal
						into zzzzClauseIntoTable_strTableNameTemp
						from #tbl_Car_Car_Filter t with (nolock)
							inner join CT_PaymentDetail cpd with (nolock)
								on t.CarId = cpd.CarId
							inner join CT_Payment cp with (nolock)
								on cpd.PaymentNo = cp.PaymentNo and cp.PaymentStatus = 'A' -- Payment must be Approved.
						--where (1=1)
						group by t.CarId
						;
				"
				, "zzzzClauseIntoTable_strTableNameTemp", strTableNameTemp
				);
		}
		private string mySql_GetClauseSelect_ForGetCarPaymentTotal(string strCarIdList)
		{
			return StringUtils.Replace(@"
						select
							t.CarId
							, IsNull(Sum(cpd.Amount), 0.0) AmountTotal
						from Car_Car t with (nolock)
							left join CT_PaymentDetail cpd with (nolock)
								on t.CarId = cpd.CarId
							left join CT_Payment cp with (nolock)
								on cpd.PaymentNo = cp.PaymentNo and cp.PaymentStatus = 'A' -- Payment must be Approved.
						where (1=1)
							zzzzClauseWhere_strCarIdList
						group by t.CarId
						;
				"
				, "zzzzClauseWhere_strCarIdList", SqlUtils.BuildClauseConditionList("and", "t.CarId", strCarIdList, "|")
				);
		}

		#endregion

		#region // Public Common:
		public DataSet CommonGetMixItem(
			string strGWUserCode
			, string strGWPassword
			, string strTid
			, string strPartnerCode
			, string strPartnerUserCode
			, string strLanguageCode
			, string strMixCode
			, object[] arrobjParamsCouple
			)
		{
			#region // Temp:
			DataSet mdsFinal = CMyDataSet.NewMyDataSet(strTid);
			int nTidSeq = 0;
			bool bNeedTransaction = false;
			string strFunctionName = "CommonGetMixItem";
			string strErrorCodeDefault = TError.ErrITS.CommonGetMixItem;
			string strParamsCoupleContents = TUtils.CUtils.ParamsCouple2String(arrobjParamsCouple, "|", "=");
			Hashtable htMixParams = CollectionUtils.ParamsCouple2Hashtable(true, arrobjParamsCouple);
			ArrayList alParamsCoupleError = new ArrayList(new object[]{
					"strFunctionName", strFunctionName
					, "strLanguageCode", strLanguageCode
					, "strMixCode", strMixCode
					, "arrobjParamsCouple", strParamsCoupleContents
					});
			#endregion

			try
			{
				#region // Init:
				_dbCarSv.AppUserID = strPartnerUserCode;
				if (bNeedTransaction) _dbCarSv.BeginTransaction();

				// Write request log:
				_log.WriteLogAsync(
					_strConfig_GwUserCode // strGwUserCodeForLog
					, _strConfig_GwUserPasswordForLog // strGwPasswordForLog
					, strTid // strAppTid
					, strPartnerCode // strAppServiceCode
					, strPartnerUserCode // strAppUserCode
					, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
					, TError.Error.NoError // strAppErrorCode
					, strFunctionName // strAppLogType1
					, "REQUEST" // strAppLogType2
					, "" // strAppLogType3
					, strLanguageCode // strAppLanguageCode
					, "" // strAppRemark
					, TUtils.CUtils.ParamsCouple2ParamsTriple(alParamsCoupleError, 1, "").ToArray() // arrobjParamsTriple
					);

				// Check Valid Tid:
				// No need.

				// Authentication: 
				// No need.
				#endregion

				#region // Switch case:
				string strResult = "";
				if (StringUtils.StringEqualIgnoreCase(strMixCode, "GetNewId")) // Case GetNewId.
				{
					strResult = myCommon_GetNewId(
						"Seq_Id" // strTableName
						, "" // strPrefix
						);
				}
				else if (StringUtils.StringEqualIgnoreCase(strMixCode, "GetTDate")) // Case GetTDate.
				{
					strResult = myUtils_GetParamsRaw(
						"TDate" // strParamCode
						);
				}
				else if (StringUtils.StringEqualIgnoreCase(strMixCode, "GetDateTimeDB")) // Case GetDateTimeDB.
				{
					strResult = TDALUtils.DBUtils.GetDateTime(_dbCarSv).ToString("yyyy-MM-dd HH:mm:ss");
				}
				else
				{
					throw CMyException.Raise(
						TError.ErrITS.CommonGetMixItem_InvalidMixCode
						, null
						, alParamsCoupleError.ToArray()
						);
				}
				CMyDataSet.SetRemark(ref mdsFinal, strResult);
				#endregion

				// Return Good:
				TDALUtils.DBUtils.CommitSafety(_dbCarSv);
				mdsFinal.AcceptChanges();
				return mdsFinal;
			}
			catch (Exception exc)
			{
				#region // Catch of try:
				// Rollback:
				TDALUtils.DBUtils.RollbackSafety(_dbCarSv);

				// Return Bad:
				return TUtils.CProcessException.Process(
					ref mdsFinal
					, exc
					, strErrorCodeDefault
					, alParamsCoupleError.ToArray()
					);
				#endregion
			}
			finally
			{
				#region // Finally of try:
				// Rollback and Release resources:
				TDALUtils.DBUtils.RollbackSafety(_dbCarSv);
				TDALUtils.DBUtils.ReleaseAllSemaphore(_dbCarSv_Sys, true);

				TUtils.CProcessException.Process(
					ref mdsFinal // mdsFinal
					, _log // log
					, _strConfig_GwUserCode // strGwUserCodeForLog
					, _strConfig_GwUserPasswordForLog // strGwPasswordForLog
					, strFunctionName // strFunctionName
					, TUtils.CUtils.TidNext(strTid, ref nTidSeq) // strAppTid
					, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
					, strPartnerCode // strAppServiceCode
					, strPartnerUserCode // strAppUserCode
					, strLanguageCode // strLanguageCode
					, "" // strRemark
					);
				#endregion
			}
		}
		public DataSet CommonSignIn(
			string strGWUserCode
			, string strGWPassword
			, string strTid
			, string strPartnerCode
			, string strPartnerUserCode
			, string strLanguageCode
			, string strDealerCode
			, string strNickName
			, string strPassword
			)
		{
			#region // Temp:
			DataSet mdsFinal = CMyDataSet.NewMyDataSet(strTid);
			int nTidSeq = 0;
			bool bNeedTransaction = false;
			string strFunctionName = "CommonSignIn";
			string strErrorCodeDefault = TError.ErrITS.CommonSignIn;
			ArrayList alParamsCoupleError = new ArrayList(new object[]{
					"strFunctionName", strFunctionName
					, "strLanguageCode", strLanguageCode
					, "strDealerCode", strDealerCode
					, "strNickName", strNickName
					});
			#endregion

			try
			{
				#region // Init:
				_dbCarSv.AppUserID = strPartnerUserCode;
				if (bNeedTransaction) _dbCarSv.BeginTransaction();

				// Write request log:
				_log.WriteLogAsync(
					_strConfig_GwUserCode // strGwUserCodeForLog
					, _strConfig_GwUserPasswordForLog // strGwPasswordForLog
					, strTid // strAppTid
					, strPartnerCode // strAppServiceCode
					, strPartnerUserCode // strAppUserCode
					, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
					, TError.Error.NoError // strAppErrorCode
					, strFunctionName // strAppLogType1
					, "REQUEST" // strAppLogType2
					, "" // strAppLogType3
					, strLanguageCode // strAppLanguageCode
					, "" // strAppRemark
					, TUtils.CUtils.ParamsCouple2ParamsTriple(alParamsCoupleError, 1, "").ToArray() // arrobjParamsTriple
					);

				// Check Valid Tid:
				//myValidateId(
				//    ref alParamsCoupleError // alParamsCoupleError
				//    , strTid // strTid
				//    , TError.ErrITS.CommonInvalidTid // strErrCodeWhenInvalid
				//    );
				#endregion

				#region // Process:
				// CheckUserExist:
				DataTable dt_Sys_User = null;
				mySys_CheckUser(
					ref alParamsCoupleError // alParamsCoupleError
					, strNickName // strUserCode
					, TConst.Flag.Active // strFlagExistToCheck
					, TConst.Flag.Active // strFlagActiveListToCheck
					, out dt_Sys_User
					);

				// CheckPassword:
				if (!StringUtils.StringEqual(strPassword, dt_Sys_User.Rows[0]["UserPassword"]))
				{
					throw CMyException.Raise(
						TError.ErrITS.CommonSignIn_InvalidPassword // strErrorCode
						, null // excInner
						, alParamsCoupleError.ToArray() // arrobjParamsCouple
						);
				}

				// Assign:
				string strUserCode = strNickName;
				CMyDataSet.SetRemark(ref mdsFinal, strUserCode);
				#endregion

				// Return Good:
				TDALUtils.DBUtils.CommitSafety(_dbCarSv);
				mdsFinal.AcceptChanges();
				return mdsFinal;
			}
			catch (Exception exc)
			{
				#region // Catch of try:
				// Rollback:
				TDALUtils.DBUtils.RollbackSafety(_dbCarSv);

				// Return Bad:
				return TUtils.CProcessException.Process(
					ref mdsFinal
					, exc
					, strErrorCodeDefault
					, alParamsCoupleError.ToArray()
					);
				#endregion
			}
			finally
			{
				#region // Finally of try:
				// Rollback and Release resources:
				TDALUtils.DBUtils.RollbackSafety(_dbCarSv);
				TDALUtils.DBUtils.ReleaseAllSemaphore(_dbCarSv_Sys, true);

				TUtils.CProcessException.Process(
					ref mdsFinal // mdsFinal
					, _log // log
					, _strConfig_GwUserCode // strGwUserCodeForLog
					, _strConfig_GwUserPasswordForLog // strGwPasswordForLog
					, strFunctionName // strFunctionName
					, TUtils.CUtils.TidNext(strTid, ref nTidSeq) // strAppTid
					, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
					, strPartnerCode // strAppServiceCode
					, strPartnerUserCode // strAppUserCode
					, strLanguageCode // strLanguageCode
					, "" // strRemark
					);
				#endregion
			}
		}

        public DataSet CommonGetMasterData(
            string strGwUserCode
            , string strGwPassword
            , string strTid
            , string strPartnerCode
            , string strPartnerUserCode
            , string strLanguageCode
            , string strTableName
            , object[] arrobjParamsTriple
            )
        {
            #region // Temp:
            DataSet mdsFinal = CMyDataSet.NewMyDataSet(strTid);
            int nTidSeq = 0;
            bool bNeedTransaction = false;
            string strFunctionName = "CommonGetMasterData";
            string strErrorCodeDefault = TError.ErrHTC.CommonGetMasterData;
            if (arrobjParamsTriple == null) arrobjParamsTriple = new object[0];
            string strParamsCoupleContents = TUtils.CUtils.ParamsTriple2String(arrobjParamsTriple, "|");
            ArrayList alParamsCoupleError = new ArrayList(new object[]{
					"strFunctionName", strFunctionName
					, "strLanguageCode", strLanguageCode
					, "strTableName", strTableName
					, "arrobjParamsCouple", strParamsCoupleContents
					});
            #endregion

            try
            {
                #region // Init:
                _dbCarSv.AppUserID = strPartnerUserCode;
                if (bNeedTransaction) _dbCarSv.BeginTransaction();

                // Write request log:
                _log.WriteLogAsync(
                    _strConfig_GwUserCode // strGwUserCodeForLog
                    , _strConfig_GwUserPasswordForLog // strGwPasswordForLog
                    , strTid // strAppTid
                    , strPartnerCode // strAppServiceCode
                    , strPartnerUserCode // strAppUserCode
                    , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
                    , TError.Error.NoError // strAppErrorCode
                    , strFunctionName // strAppLogType1
                    , "REQUEST" // strAppLogType2
                    , "" // strAppLogType3
                    , strLanguageCode // strAppLanguageCode
                    , "" // strAppRemark
                    , TUtils.CUtils.ParamsCouple2ParamsTriple(alParamsCoupleError, 1, "").ToArray() // arrobjParamsTriple
                    );

                // Check Valid Tid:
                myUtils_ValidateId(
                    ref alParamsCoupleError // alParamsCoupleError
                    , strTid // strTid
                    , TError.ErrITS.CommonInvalidTid // strErrCodeWhenInvalid
                    );
                #endregion

                #region // Check:
                // Check supported tables:
                strTableName = strTableName.ToUpper();
                ArrayList alTablesSupported = myCommon_GetSupportedTable();
                if (!alTablesSupported.Contains(strTableName))
                {
                    throw CMyException.Raise(
                        TError.ErrHTC.CommonGetMasterData_TableNameNotSupported
                        , null
                        , alParamsCoupleError.ToArray()
                        );
                }

                // Check injection Sql:
                for (int i = 0; i < arrobjParamsTriple.Length; i++)
                {
                    arrobjParamsTriple[i] = SqlUtils.ProtectInjection(Convert.ToString(arrobjParamsTriple[i]));
                }
                #endregion

                #region // Process:
                DataTable dtResult = TDALUtils.DBUtils.GetTableContents(
                    _dbCarSv // db
                    , strTableName // strTableName
                    , "*" // strColumnList
                    , "" // strClauseOrderBy
                    , arrobjParamsTriple // arrobjParamsTriple
                    );
                dtResult.TableName = strTableName.ToUpper();
                DataUtils.MoveDataTable(ref mdsFinal, ref dtResult);
                #endregion

                // Return Good:
                TDALUtils.DBUtils.CommitSafety(_dbCarSv);
                mdsFinal.AcceptChanges();
                return mdsFinal;
            }
            catch (Exception exc)
            {
                #region // Catch of try:
                // Rollback:
                TDALUtils.DBUtils.RollbackSafety(_dbCarSv);

                // Return Bad:
                return TUtils.CProcessException.Process(
                    ref mdsFinal
                    , exc
                    , strErrorCodeDefault
                    , alParamsCoupleError.ToArray()
                    );
                #endregion
            }
            finally
            {
                #region // Finally of try:
                // Rollback and Release resources:
                TDALUtils.DBUtils.RollbackSafety(_dbCarSv);
                TDALUtils.DBUtils.ReleaseAllSemaphore(_dbCarSv_Sys, true);

                TUtils.CProcessException.Process(
                    ref mdsFinal // mdsFinal
                    , _log // log
                    , _strConfig_GwUserCode // strGwUserCodeForLog
                    , _strConfig_GwUserPasswordForLog // strGwPasswordForLog
                    , strFunctionName // strFunctionName
                    , TUtils.CUtils.TidNext(strTid, ref nTidSeq) // strAppTid
                    , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
                    , strPartnerCode // strAppServiceCode
                    , strPartnerUserCode // strAppUserCode
                    , strLanguageCode // strLanguageCode
                    , "" // strRemark
                    );
                #endregion
            }
        }
        public DataSet CommonSaveMasterData(
            string strGwUserCode
            , string strGwPassword
            , string strTid
            , string strPartnerCode
            , string strPartnerUserCode
            , string strLanguageCode
            , string[] arrstrTableNamesCouple
            , DataSet dsData
            )
        {
            #region // Temp:
            DataSet mdsFinal = CMyDataSet.NewMyDataSet(strTid);
            int nTidSeq = 0;
            bool bNeedTransaction = true;
            string strFunctionName = "CommonSaveMasterData";
            string strErrorCodeDefault = TError.ErrHTC.CommonSaveMasterData;
            if (arrstrTableNamesCouple == null) arrstrTableNamesCouple = new string[0];
            string strTableNamesCoupleContents = TUtils.CUtils.ParamsCouple2String(arrstrTableNamesCouple, "=", "|");
            Hashtable htMixParams = CollectionUtils.ParamsCouple2Hashtable(true, arrstrTableNamesCouple);
            ArrayList alParamsCoupleError = new ArrayList(new object[]{
					"strFunctionName", strFunctionName
					, "strLanguageCode", strLanguageCode
					, "strTableNamesCoupleContents", strTableNamesCoupleContents
					, "dsData", XmlUtils.DataSet2Xml(dsData, XmlWriteMode.IgnoreSchema)
					});
            #endregion

            try
            {
                #region // Init:
                _dbCarSv.AppUserID = strPartnerUserCode;
                if (bNeedTransaction) _dbCarSv.BeginTransaction();

                // Write request log:
                _log.WriteLogAsync(
                    _strConfig_GwUserCode // strGwUserCodeForLog
                    , _strConfig_GwUserPasswordForLog // strGwPasswordForLog
                    , strTid // strAppTid
                    , strPartnerCode // strAppServiceCode
                    , strPartnerUserCode // strAppUserCode
                    , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
                    , TError.Error.NoError // strAppErrorCode
                    , strFunctionName // strAppLogType1
                    , "REQUEST" // strAppLogType2
                    , "" // strAppLogType3
                    , strLanguageCode // strAppLanguageCode
                    , "" // strAppRemark
                    , TUtils.CUtils.ParamsCouple2ParamsTriple(alParamsCoupleError, 1, "").ToArray() // arrobjParamsTriple
                    );

                // Check Valid Tid:
                myUtils_ValidateId(
                    ref alParamsCoupleError // alParamsCoupleError
                    , strTid // strTid
                    , TError.ErrITS.CommonInvalidTid // strErrCodeWhenInvalid
                    );
                #endregion

                #region // Check:
                // Check TableNames and SupportTables:
                ArrayList alTablesSupported = myCommon_GetSupportedTable();
                for (int i = 0; i < arrstrTableNamesCouple.Length; i += 2)
                {
                    if (!dsData.Tables.Contains(arrstrTableNamesCouple[i + 1]))
                    {
                        alParamsCoupleError.AddRange(new object[] { 
								"strTableNameNotFound", arrstrTableNamesCouple[i + 1]
							});
                        throw CMyException.Raise(
                            TError.ErrHTC.CommonSaveMasterData_TableNotFound
                            , null
                            , alParamsCoupleError.ToArray()
                            );
                    }
                    if (!alTablesSupported.Contains(arrstrTableNamesCouple[i].ToUpper()))
                    {
                        alParamsCoupleError.AddRange(new object[] { 
								"strTableNameNotSupported", arrstrTableNamesCouple[i]
							});
                        throw CMyException.Raise(
                            TError.ErrHTC.CommonSaveMasterData_TableNotSupported
                            , null
                            , alParamsCoupleError.ToArray()
                            );
                    }
                }
                #endregion

                #region // Process:
                for (int i = 0; i < arrstrTableNamesCouple.Length; i += 2)
                {
                    //// Preprocess:
                    DataTable dtRefine = dsData.Tables[arrstrTableNamesCouple[i + 1]].GetChanges();
                    //for (int k = 0; k < dtRefine.Rows.Count; k++)
                    //{
                    //    if (dtRefine.Rows[k].RowState == DataRowState.Added || dtRefine.Rows[k].RowState == DataRowState.Modified)
                    //    {
                    //        for (int nCol = 0; nCol < dtRefine.Columns.Count; nCol++)
                    //        {
                    //            if (dtRefine.Columns[nCol].DataType == typeof(string) && dtRefine.Rows[k][nCol] != DBNull.Value)
                    //            {
                    //                dtRefine.Rows[k][nCol] = Convert.ToString(dtRefine.Rows[k][nCol]).Trim();
                    //            }
                    //        }
                    //    }
                    //}

                    // SaveData:
                    _dbCarSv.SaveData(arrstrTableNamesCouple[i], dtRefine);

                    #region // SpecialRecheck:
                    /*
					if (StringUtils.StringEqualIgnoreCase(arrstrTableNamesCouple[i], "Mst_CarPrice"))
					{
						// Build Sql:
						string strSql_Mst_CarPrice = @"
								select 
									t.SpecCode
									, Count(0) MyCount
								from Mst_CarPrice t with (nolock)
								where
									t.FlagActive = '1'
								group by t.SpecCode
								having Count(0) > 1
								;
							";

						// Get Data:
						DataTable dt_Mst_CarPrice = _dbCarSv.ExecQuery(strSql_Mst_CarPrice).Tables[0];
						if (dt_Mst_CarPrice.Rows.Count > 0)
						{
							alParamsCoupleError.AddRange(new object[] { 
								"ErrTableName", arrstrTableNamesCouple[i]
								, "ErrSpecCode", dt_Mst_CarPrice.Rows[0]["SpecCode"]
								, "ErrCount", dt_Mst_CarPrice.Rows[0]["MyCount"]
							});
							throw CMyException.Raise(
								TError.ErrHTC.CommonSaveMasterData_SpecialRecheck
								, null
								, alParamsCoupleError.ToArray()
								);
						}
					}
					*/
                    #endregion
                }
                #endregion

                // Return Good:
                TDALUtils.DBUtils.CommitSafety(_dbCarSv);
                mdsFinal.AcceptChanges();
                return mdsFinal;
            }
            catch (Exception exc)
            {
                #region // Catch of try:
                // Rollback:
                TDALUtils.DBUtils.RollbackSafety(_dbCarSv);

                // Return Bad:
                return TUtils.CProcessException.Process(
                    ref mdsFinal
                    , exc
                    , strErrorCodeDefault
                    , alParamsCoupleError.ToArray()
                    );
                #endregion
            }
            finally
            {
                #region // Finally of try:
                // Rollback and Release resources:
                TDALUtils.DBUtils.RollbackSafety(_dbCarSv);
                TDALUtils.DBUtils.ReleaseAllSemaphore(_dbCarSv_Sys, true);

                TUtils.CProcessException.Process(
                    ref mdsFinal // mdsFinal
                    , _log // log
                    , _strConfig_GwUserCode // strGwUserCodeForLog
                    , _strConfig_GwUserPasswordForLog // strGwPasswordForLog
                    , strFunctionName // strFunctionName
                    , TUtils.CUtils.TidNext(strTid, ref nTidSeq) // strAppTid
                    , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") // strAppTDateTime
                    , strPartnerCode // strAppServiceCode
                    , strPartnerUserCode // strAppUserCode
                    , strLanguageCode // strLanguageCode
                    , "" // strRemark
                    );
                #endregion
            }
        }
		#endregion
	}
}
